package net.goutalk.glcs.module.system.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.DbRuntimeException;
import cn.hutool.db.DbUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.meta.Column;
import cn.hutool.db.meta.MetaUtil;
import cn.hutool.db.meta.Table;
import cn.hutool.db.meta.TableType;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.creator.DruidDataSourceCreator;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.druid.DruidConfig;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.baomidou.mybatisplus.extension.toolkit.JdbcUtils;
import net.goutalk.glcs.common.exception.MyException;
import net.goutalk.glcs.common.model.datasource.MyColumnInfo;
import net.goutalk.glcs.common.model.datasource.MyTableInfo;
import net.goutalk.glcs.common.utils.DatasourceUtil;
import net.goutalk.glcs.common.utils.JdbcToJavaUtil;
import net.goutalk.glcs.module.system.dto.AddDatabaseLinkDto;
import net.goutalk.glcs.module.system.dto.UpdateDatabaseLinkDto;
import net.goutalk.glcs.module.system.entity.Databaselink;
import net.goutalk.glcs.module.system.mapper.DatabaselinkMapper;
import net.goutalk.glcs.module.system.service.IDatabaselinkService;
import lombok.AllArgsConstructor;
import lombok.SneakyThrows;
import oracle.jdbc.OracleConnection;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 数据库连接表 服务实现类
 * </p>
 *
 * @author tanyujie
 * @since 2022-03-04
 */
@Service
@AllArgsConstructor
public class DatabaselinkServiceImpl extends ServiceImpl<DatabaselinkMapper, Databaselink> implements IDatabaselinkService {

    private final DynamicRoutingDataSource routingDataSource;

    private final DruidDataSourceCreator druidDataSourceCreator;

    private final DynamicDataSourceProperties dynamicDataSourceProperties;

    @Override
    public Boolean add(AddDatabaseLinkDto dto) {
        long count = count(Wrappers.<Databaselink>query().lambda().eq(Databaselink::getDbName,dto.getDbName()));
        if(count > 0){
            throw new MyException("数据库名称已经存在！");
        }

        Databaselink databaselink = BeanUtil.toBean(dto, Databaselink.class);

        if(!test(databaselink)){
            throw new MyException("数据库无法连接，请检查参数信息！");
        }
        save(databaselink);
        addDynamicDataSource(databaselink);
        return Boolean.TRUE;
    }

    @Override
    public Boolean update(UpdateDatabaseLinkDto dto) {
        long count = count(Wrappers.<Databaselink>query().lambda().eq(Databaselink::getDbName,dto.getDbName()).ne(Databaselink::getId,dto.getId()));
        if(count > 0){
            throw new MyException("数据库名称已经存在！");
        }

        Databaselink databaselink = BeanUtil.toBean(dto, Databaselink.class);

        if(!test(databaselink)){
            throw new MyException("数据库无法连接，请检查参数信息！");
        }

        // 删除动态数据源
        routingDataSource.removeDataSource(StrUtil.toString(dto.getId()));
        // 添加动态数据源
        addDynamicDataSource(databaselink);
        return Boolean.TRUE;
    }

    @Override
    @SneakyThrows
    public Boolean test(Databaselink databaselink) {
        return DatasourceUtil.testConnection(databaselink.getHost(), databaselink.getUsername(), databaselink.getPassword());
    }

    @Override
    @SneakyThrows
    public List<MyTableInfo> getTables(String id, String tableName) {
        DataSource dataSource = DatasourceUtil.getDataSource(id.toString());

        Collection<Column> columns;


        if (StrUtil.isNotBlank(tableName)) {
            tableName = StringPool.PERCENT + tableName + StringPool.PERCENT;

            Table tableMeta = MetaUtil.getTableMeta(dataSource, tableName);

            columns = tableMeta.getColumns();
        } else {
            tableName = null;
            columns= new ArrayList<>();

        }

        List<MyTableInfo> myTableInfos = new ArrayList<>();


        Optional<Column> pkOptional = columns.stream().filter(Column::isPk).findFirst();
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            final DatabaseMetaData metaData = connection.getMetaData();
            // 获得表元数据（表注释）
            String catalog = connection.getCatalog();
            String schema = connection.getSchema();
            Connection metaConn = metaData.getConnection();
            // oracle这只查询出表备注
            if (metaConn instanceof OracleConnection) ((OracleConnection) metaConn).setRemarksReporting(true);
            try (ResultSet rs = metaData.getTables(catalog, schema, tableName, new String[]{TableType.TABLE.value()})) {
                if (null != rs) {
                    while (rs.next()) {
                        MyTableInfo myTableInfo = new MyTableInfo();

                        pkOptional.ifPresent(pk -> {
                            myTableInfo.setPkField(pk.getName());
                            myTableInfo.setPkType(pk.getTypeEnum().name());
                        });

                        myTableInfo.setTableName(rs.getString("TABLE_NAME"));
                        myTableInfo.setTableComment(rs.getString("REMARKS"));
                        myTableInfos.add(myTableInfo);
                    }
                }
            }
        } catch ( SQLException e) {
            throw new DbRuntimeException("Get columns error!", e);
        } finally {
            DbUtil.close(connection);
        }
        return myTableInfos;
    }

    @Override
    @SneakyThrows
    public List<Entity> getTablesData(String id, String tableName) {
        DataSource dataSource = DatasourceUtil.getDataSource(id);

        return DbUtil.use(dataSource).findAll(tableName);
    }

    @Override
    public List<MyColumnInfo> getTableColumns(String id, String tableName) {
        DataSource dataSource = DatasourceUtil.getDataSource(id);

        Table tableMeta = MetaUtil.getTableMeta(dataSource, tableName);
        Collection<Column> columns = tableMeta.getColumns();

        List<MyColumnInfo> myColumnInfos = new ArrayList<>();
        for (Column column : columns) {
            MyColumnInfo myColumnInfo = new MyColumnInfo();
            myColumnInfo.setColumn(column.getName());
            myColumnInfo.setColumnComment(column.getComment());
            myColumnInfo.setDataType(JdbcToJavaUtil.getClassName(column));
            myColumnInfo.setNullable(column.isNullable());
            myColumnInfo.setPrimaryKey(column.isPk());
            myColumnInfo.setAutoIncrement(column.isAutoIncrement());
            myColumnInfo.setDataLength(column.getSize());
            myColumnInfo.setColumnDefault(column.getColumnDef());
            myColumnInfos.add(myColumnInfo);
        }
        return myColumnInfos;
    }

    @Override
    public List<String> getTableColumnName(String id, String tableName) {
        DataSource dataSource = DatasourceUtil.getDataSource(id);

        Table tableMeta = MetaUtil.getTableMeta(dataSource, tableName);
        Collection<Column> columns = tableMeta.getColumns();

        return columns.stream().map(Column::getName).collect(Collectors.toList());
    }

    @Override
    public Map<String, List<MyColumnInfo>> getMultiTableColumns(String id, String tableNames) {
        DataSource dataSource = DatasourceUtil.getDataSource(id);

        Map<String, List<MyColumnInfo>> result = new HashMap<>();

        List<String> allTables = StrUtil.split(tableNames, ",");
        for (String tableName : allTables) {
            Table tableMeta = MetaUtil.getTableMeta(dataSource, tableName);
            Collection<Column> columns = tableMeta.getColumns();

            List<MyColumnInfo> myColumnInfos = new ArrayList<>();
            for (Column column : columns) {
                MyColumnInfo myColumnInfo = new MyColumnInfo();
                myColumnInfo.setColumn(column.getName());
                myColumnInfo.setColumnComment(column.getComment());
                myColumnInfo.setDataType(JdbcToJavaUtil.getClassName(column));
                myColumnInfo.setNullable(column.isNullable());
                myColumnInfo.setPrimaryKey(column.isPk());
                myColumnInfo.setAutoIncrement(column.isAutoIncrement());
                myColumnInfo.setDataLength(column.getSize());
                myColumnInfo.setColumnDefault(column.getColumnDef());
                myColumnInfos.add(myColumnInfo);
            }
            result.put(tableName, myColumnInfos);
        }

        return result;
    }

    @Override
    public Map<String, List<String>> getMultiTableColumnName(String id, String tableNames) {
        DataSource dataSource = DatasourceUtil.getDataSource(id);

        Map<String, List<String>> result = new HashMap<>();

        List<String> allTables = StrUtil.split(tableNames, ",");

        for (String tableName : allTables) {
            Table tableMeta = MetaUtil.getTableMeta(dataSource, tableName);
            Collection<Column> columns = tableMeta.getColumns();

            result.put(tableName, columns.stream().map(Column::getName).collect(Collectors.toList()));
        }

        return result;
    }

    /**
     * 添加动态数据源
     * @param databaselink
     */
    private void addDynamicDataSource(Databaselink databaselink){

        DataSourceProperty property = new DataSourceProperty();
        property.setUsername(databaselink.getUsername());
        property.setPassword(databaselink.getPassword());
        property.setUrl(databaselink.getHost());
        property.setDriverClassName(databaselink.getDriver());


        DruidConfig druidConfig = property.getDruid();
        druidConfig.setInitialSize(10); // 初始化大小
        druidConfig.setMaxActive(100);// 最大连接池
        druidConfig.setMinIdle(10);// 最小连接池
        druidConfig.setMaxWait(60000); //最大等待超时时间
        druidConfig.setPoolPreparedStatements(false); // 是否缓存preparedStatement，也就是PSCache  官方建议MySQL下建议关闭   个人建议如果想用SQL防火墙 建议打开
        druidConfig.setMaxPoolPreparedStatementPerConnectionSize(20);//是否缓存preparedStatement，也就是PSCache  官方建议MySQL下建议关闭   个人建议如果想用SQL防火墙 建议打开
        druidConfig.setTimeBetweenEvictionRunsMillis(60000L);// 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        druidConfig.setMinEvictableIdleTimeMillis(300000L); //  配置一个连接在池中最小生存的时间，单位是毫秒
        if(property.getUrl().contains("oracle")){
            druidConfig.setValidationQuery("SELECT 1 FROM DUAL");
        }
        else{
            druidConfig.setValidationQuery("SELECT 1 "); //测试链接 语句
        }
        druidConfig.setTestWhileIdle(true);
        druidConfig.setTestOnReturn(false);
        druidConfig.setTestOnBorrow(false);
        druidConfig.setFilters("stat,slf4j"); // #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        druidConfig.setUseGlobalDataSourceStat(true);
        Properties properties =new Properties();
        properties.put("druid.stat.mergeSql",true); //打开mergeSql功能
        properties.put("druid.stat.slowSqlMillis",true); // 打开慢sql 记录功能
        druidConfig.setConnectionProperties(properties);

        DataSource dataSource = druidDataSourceCreator.createDataSource(property);
        routingDataSource.addDataSource(StrUtil.toString(databaselink.getId()), dataSource);
    }

    public DbType getDbType(String id) {
        String url = null;
        if (StrUtil.equalsIgnoreCase(id, dynamicDataSourceProperties.getPrimary())) {
            url = dynamicDataSourceProperties.getDatasource().get(dynamicDataSourceProperties.getPrimary()).getUrl();
        } else {
            Databaselink databaselink = this.getById(id);
            if (databaselink != null) {
                url = databaselink.getHost();
            }
        }
        return JdbcUtils.getDbType(url);
    }
}
